# 1.定义一个方法
import json
import logging
import traceback

import pymysql
from bs4 import BeautifulSoup
from pymysql.constants import CLIENT

import config


def exe_sql(sql_str, database=None):
    conn = None
    cur = None
    try:
        # 2.创建数据库连接对象
        conn = pymysql.connect(host="121.43.169.97", user="root", password="Itcast_p2p_20191228", database=database,
                               client_flag=CLIENT.MULTI_STATEMENTS)

        # 3.创建游标对象
        cur = conn.cursor()

        # 4.在游标对象中执行SQL语句
        cur.execute(sql_str)

        # 如果是查询语句
        if sql_str.split(" ")[0].lower() == "select":
            # 返回全部查询结果
            return cur.fetchall()
        # 如果是非查询语句
        else:
            # 手动提交数据事务
            conn.commit()
            # 返回受影响的行数
            return "受影响的行数:{}".format(cur.rowcount)
    except Exception as e:
        # 有异常的情况下，进行数据库事务回滚
        if conn is not None:
            conn.rollback()
        print(e)
    finally:
        # 5.关闭游标对象
        if cur is not None:
            cur.close()
        # 6.关闭数据库连接对象
        if conn is not None:
            conn.close()


# 封装清洗用户信息的方法
def clear_user_info():
    # 需要执行SQL语句
    sql1 = "delete i.* from mb_member_info i inner join mb_member m on m.id = i.member_id where m.phone in ('15814662511','15814662512','15814662513','15814662514','15814662515','15814662516','15814662517','15814662518','15814662520');"
    sql2 = "delete l.* from mb_member_login_log l inner join mb_member m on m.id=l.member_id where m.phone in ('15814662511','15814662512','15814662513','15814662514','15814662515','15814662516','15814662517','15814662518','15814662520')"
    sql3 = "delete from mb_member where phone in ('15814662511','15814662512','15814662513','15814662514','15814662515','15814662516','15814662517','15814662518','15814662520')"
    sql4 = "delete from mb_member_register_log where phone in ('15814662511','15814662512','15814662513','15814662514','15814662515','15814662516','15814662517','15814662518','15814662520')"
    # 调用封装操作数据库的方法，执行SQL语句
    print(exe_sql(sql1, "czbk_member"))
    print(exe_sql(sql2, "czbk_member"))
    print(exe_sql(sql3, "czbk_member"))
    print(exe_sql(sql4, "czbk_member"))


# 公共断言的方法
def common_assert(testCase, response, exp_status_code, exp_status, exp_desp, case_name=""):
    # 获取字典类型的响应体数据
    res_dict = response.json()
    try:
        # 断言状态码
        testCase.assertEqual(exp_status_code, response.status_code)
        # 断言status
        testCase.assertEqual(exp_status, res_dict.get("status"))
        # 断言description
        testCase.assertIn(exp_desp, res_dict.get("description"))
    except:
        logging.error("用例执行失败：{}".format(case_name))
        logging.error(traceback.format_exc())


# 封装读取测试数据的方法
def read_json(fileName, apiName):
    # 获取测试数据的文件路径
    file_path = config.BASE_PATH + "/data/" + fileName + ".json"
    # 读取测试数据
    with open(file_path, encoding="utf8") as f:
        # 将测试数据解析为字典类型
        data = json.load(f)

    # 获取指定接口的测试数据
    api_data = data.get(apiName)
    print(api_data)
    # 定义一个新的列表
    api_data_list = list()
    # 列表嵌套字典的测试数据转化为列表嵌套元组
    for case_data in api_data:
        case_data_tuple = tuple(case_data.values())
        api_data_list.append(case_data_tuple)

    # 返回最新格式的测试数据
    return api_data_list


# html解析工具
def html_util(response):
    # 1.从响应结果中，获取请求的标签数据
    data = response.json().get("description").get("form")
    logging.info("获取的请求的标签数据：{}".format(data))
    # 2.创建beautifulsoup对象
    bs = BeautifulSoup(data, "html.parser")
    # 3.从form标签中获取url
    url = bs.form.get("action")
    logging.info("提取的第三方接口的url:{}".format(url))
    # 4. 从input标签中获取请求体数据
    # 4.1 定义一个字典，用来接收请求体的数据
    form_dict = dict()
    # 4.2 获取全部的input标签
    input_all = bs.find_all("input")
    # 4.3 循环遍历出每一个input标签
    for input_sample in input_all:
        # 4.4从input标签中获取请求体的字段名
        key = input_sample.get("name")
        # 4.5从input标签中获取请求体的字段值
        value = input_sample.get("value")
        # 4.6.将字段名和值放入定义的请求体数据的字典中
        form_dict[key] = value
    logging.info("提取的第三方接口的请求体数据:{}".format(form_dict))
    # 7.返回，带有url和请求体数据的列表
    return [url, form_dict]


# 封装初始化借款标数据的方法
def init_tender_info():
    # SQL语句
    sql = '''
        delete from `czbk_member`.`mb_member` where id=869;
        delete from `czbk_member`.`mb_member_info` where id=987;
        delete from `czbk_finance`.`fn_loan_amount` where id=987;
        delete from `p2p_mock`.`p2p_account` where id=911;
        delete from `czbk_finance`.`fn_loan` where id=642;
        delete from `czbk_finance`.`fn_loan_info` where id=832;
        delete from `czbk_finance`.`fn_loan_amount_log` where id=869;
        INSERT INTO `czbk_member`.`mb_member` (`id`, `name`, `trust_account`, `password`, `email`, `phone`, `paypassword`, `count`, `register_ip`, `register_time`, `register_date`, `lastlogin_ip`, `lastlogin_time`, `lastlogin_date`, `register_type`, `role`, `group`, `group_status`, `is_realname`, `is_email`, `is_phone`, `is_video`, `is_id5`, `is_vip`, `credit_point`, `is_auto`, `pwd_attach`, `status`, `lock_time`, `vip_start_time`, `vip_end_time`, `vip_category_id`, `vip_category_ind`, `amount`, `phonestr`) VALUES ('869', '13099775533', '6021234507160403', 'eab70f4a46c640b3dfc35040bf37c803', NULL, '13099775533', NULL, '1', '1972826569', '1588835827', '2020-05-07', '1972826569', '1588835827', '2020-05-07', '1', '1', '1', '2', '1', '-1', '1', '-1', '-1', '-1', '0', '-1', '1m4U82PqPx', '1', NULL, NULL, NULL, NULL, NULL, '0.00', NULL);
        INSERT INTO `czbk_member`.`mb_member_info` (`id`, `member_id`, `member_name`, `avatar`, `nickname`, `realname`, `gender`, `birthday`, `card_id`, `qq`, `educational_background`, `marry_status`, `graduated`, `company_industry`, `company_scale`, `company_office`, `monthly_income`, `invite_money`, `hometown_province`, `hometown_city`, `hometown_area`, `hometown_post`) VALUES ('987', '869', '13099775533', NULL, NULL, '小石头', '男', '1986-11-18', '429009118611181111', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
        INSERT INTO `czbk_finance`.`fn_loan_amount` (`id`, `member_id`, `member_name`, `credit_amount`, `credit_amount_freeze`, `vouch_amount`, `vouch_amount_freeze`, `pawn_amount`, `pawn_amount_freeze`, `roam_amount`, `roam_amount_freeze`) VALUES ('987', '869', '13099775533', '200000.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000');
        INSERT INTO `p2p_mock`.`p2p_account` (`id`, `account`, `AvlBal`, `AcctBal`, `FrzBal`, `create_time`) VALUES ('911', '6021234507160403', '0.00', '0.00', '0.00', '2020-05-07 16:04:03');
        INSERT INTO `czbk_finance`.`fn_loan` (`id`, `ind`, `serialno`, `name`, `member_id`, `member_name`, `amount`, `credited_amount`, `progress`, `tender_count`, `category_id`, `category_type`, `repay_type`, `period`, `apr`, `sort_index`, `sort_top`, `status`, `hidden_status`, `additional_status`, `deposit_certificate`, `certificate_file_id`, `loan_repay_status`, `loan_repay_time`, `overdue_time`, `add_date`, `add_time`, `verify_time`, `reverify_time`, `add_ip`, `vouch_company_id`, `op_status`, `marker_type`) VALUES ('642', 'f5380d59791773a8abad4141ff90a5a4', '202008231111', '数据库构造借款1', '869', '13099775533', '5000.00', '0.00', '0.00', '0', '1', '1', '1', '60', '7.55', '1', NULL, '3', '1', '-1', '-1', NULL, NULL, NULL, '1640962392', '2020-05-07', '1588847800', '1588851413', NULL, '1972826569', '0', '-1', '数据库');
        INSERT INTO `czbk_finance`.`fn_loan_info` (`id`, `loan_id`, `thumbs`, `contents`, `attachment_ids`, `password`, `use`, `tender_amount_min`, `tender_amount_max`, `freeze_amount`, `freeze_amount_proportion`, `freeze_period`, `award_status`, `fail_award_status`, `award_amount`, `award_proportion`, `award_amount_total`, `validate`, `part_status`, `tender_count`, `comment_status`, `comment_count`, `is_company`, `company_name`, `vouch_company_info`, `vouch_company_pic`, `vouch_company_guaranty`, `amount_category_id`, `hits`, `cancel_admin_id`, `cancel_remark`, `cancel_time`, `verify_admin_id`, `verify_admin_name`, `verify_remark`, `verify_time`, `verify_ip`, `reverify_admin_id`, `reverify_admin_name`, `reverify_remark`, `reverify_time`, `reverify_ip`, `auto_scale`, `is_auto`, `additional_status`, `additional_apr`, `additional_name`, `additional_amount_max`, `additional_pic`, `product_process`, `information`) VALUES ('832', '642', NULL, '数据库构造借款1', 'a:0:{}', NULL, '10102', '100.00', '1000.00', NULL, '10.00', NULL, '-1', '-1', NULL, NULL, NULL, '239', NULL, '0', '-1', '0', '-1', NULL, NULL, NULL, NULL, '1', '1', NULL, NULL, NULL, '4', 'admin', '数据库构造借款1 通过', '1588851413', '1972826569', NULL, NULL, NULL, NULL, NULL, NULL, '1', '-1', '0.000000', NULL, '0.000000', NULL, NULL, NULL);
        UPDATE `czbk_finance`.`fn_loan_amount` SET `credit_amount_freeze`='100000.000000' WHERE `member_id`='869';
        INSERT INTO `czbk_finance`.`fn_loan_amount_log` (`member_id`, `member_name`, `category_id`, `type`, `amount`, `remark`, `add_time`) VALUES ('869','13099775533', 1, 4, '100000', '借款冻结', '1972826569');	
    '''
    # 执行SQL语句
    res = exe_sql(sql)
    return res


if __name__ == '__main__':
    print(init_tender_info())
